package dao;

import beans.Servico;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class ServicoDAO {
    
    PreparedStatement pst;
    ResultSet rs;
    
    public ServicoDAO() {
    }
    
    public Servico load(int idDespesa) {
        Servico srv = new Servico();

        String query = "SELECT * FROM servico WHERE idDespesa = ?";

        try {
            pst = Conexao.getConexao().prepareStatement(query);
            pst.setInt(1, idDespesa);

            rs = pst.executeQuery();

            if (rs.next()) {
                srv.setDescricao(rs.getString("descricao"));
                srv.setDataRealizada(rs.getDate("dataRealizada"));
                srv.setIdDespesa(rs.getInt("idDespesa"));
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                            ex.printStackTrace();
            }
        }

        return srv;
    }
    
    public void insert(Servico srv) {
        String query = "INSERT INTO servico (descricao, dataRealizada, idDespesa)"
                + " VALUES(?, ?, ?)";

        try {
            pst = Conexao.getConexao().prepareStatement(query);

            pst.setString(1, srv.getDescricao());
            pst.setDate(2, srv.getDataRealizada());
            pst.setInt(3, srv.getIdDespesa());

            pst.executeUpdate();

        } catch (Exception ex) {
                                        ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                            ex.printStackTrace();
            }
        }
    }

    private void update(Servico srv) {
        String query = "UPDATE servico"
                + "SET descricao = ?, dataRealizada = ?"
                + "WHERE idDespesa = ?";

        try {
            pst = Conexao.getConexao().prepareStatement(query);

            pst.setString(1, srv.getDescricao());
            pst.setDate(2, srv.getDataRealizada());
            pst.setInt(3, srv.getIdDespesa());

            pst.executeUpdate();

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }


    }

    public void save(Servico srv) {
        String query = "SELECT COUNT(*) FROM servico WHERE idDespesa = ?";

        try {
            pst = Conexao.getConexao().prepareStatement(query);
            pst.setInt(1, srv.getIdDespesa());
            
            rs = pst.executeQuery();
            rs.next();

            if (rs.getInt("count") == 0) 
                insert(srv);
            else
                update(srv);
            

        } catch (SQLException ex) {
            ex.printStackTrace();

        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }
    }
    

    public void delete(Servico srv) {
        String query = "DELETE FROM servico WHERE idDespesa = ?";

        try {
            pst = Conexao.getConexao().prepareStatement(query);
            pst.setInt(1, srv.getIdDespesa());

            pst.executeUpdate();

        } catch (SQLException ex) {
            ex.printStackTrace();

        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }
    }
    
    public ArrayList<Servico> loadAll() {
        ArrayList<Servico> servicos = new ArrayList<Servico>();

        String query = "SELECT * FROM servico";

        try {
            pst = Conexao.getConexao().prepareStatement(query);
            rs = pst.executeQuery();

            while (rs.next()) {
                Servico srv = new Servico();

                srv.setDescricao(rs.getString("descricao"));
                srv.setDataRealizada(rs.getDate("dataRealizada"));
                srv.setIdDespesa(rs.getInt("idDespesa"));

                servicos.add(srv);
            }
        } catch (SQLException ex) {
            ex.getLocalizedMessage();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }

        return servicos;
    }
    
}
